#!/usr/bin/perl -w

package conf;
    use strict;
    use DBI;
    use utf8;
    use URI::Escape;

	require '../sub/subMail.pl' ;
	require '../conf/config.pl';
    
    our %db_conf = %conf::db_conf ; 
    my $db_host = $db_conf{'db_host'};
    my $db_user = $db_conf{'db_user'};
    my $db_pwd = $db_conf{'db_pwd'}; 

    # Connect to target DB
    my $dbh = DBI->connect("$db_host","$db_user","$db_pwd", {'RaiseError' => 1,'mysql_enable_utf8'=>1});

    my $sqlString="";
    my $subject="";
    my $contents="";
    my $receipt="";
    
  

	#获取最近1个小时关闭的报障
	$sqlString = <<"___SQL___";
select t.tn as no,
IFNULL(cu.first_name,replace(replace(replace(t.customer_user_id,'"',''),'<',''),'>','')) as customer,
t.title as subject,u.first_name as engineer,t.create_time as time,
IFNULL(cu.email,replace(t.customer_user_id,'"','')) as customer_mail,
a.id as articleid 
from ticket t
left join customer_user cu on cu.customer_id=t.customer_user_id left join users u on u.id=t.user_id  
left join article a on t.id=a.ticket_id left join queue q on t.queue_id=q.id
where  
t.create_time<>a.create_time
and a.article_type_id=1
and q.name in ('X5','IT-Simon','IT-Jake','IT-Peter','IT-Jerry','IT-Ming')
and UPPER(a.a_from) not like CONCAT('%',UPPER(t.customer_user_id),'%')
and a.create_time>NOW()-INTERVAL 5 MINUTE 
___SQL___

    my $sqr = $dbh->prepare("$sqlString");
    $sqr->execute();

    while(my $ref = $sqr->fetchrow_hashref()) {
    	
		my $subsqr = $dbh->prepare("select a.a_body as body,a_from as sender from ticket t left join article a on (a.ticket_id=t.id) where t.tn='$ref->{no}' and a.id=$ref->{articleid} order by a.change_time");
	
		$subsqr->execute();
		my $subcontents="";
		while(my $subref = $subsqr->fetchrow_hashref()) {
			$subcontents.="$subref->{sender}:\n";
			$subcontents.="---------------------------------------------------------------------------\n";
			$subcontents.="$subref->{body}\n \n";
	
		}    	
    	my $customer=$ref->{'customer'};
    	$receipt="$ref->{'customer_mail'}";
    	
		use URI::URL;
		my $urlReply="[Ticket#$ref->{'no'}] Update";
		$urlReply = URI::URL->new( $urlReply );
		$urlReply =~ s/#/%23/g;
		
    	$subject="[Ticket#$ref->{'no'}] 报障更新通知-用户 （$ref->{'subject'}）";
		$contents = <<"___CONTENTS___";
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">  
		
<style type='text/css'>div{border:0px solid #CCC;} div.h{float:left;width:80px;margin:auto;overflow-x:hidden;} body {background-color:#D5D5D5} table.header {font-size:13pt;background-color:#F3F3F3; width:80%; height:8;border-top: 2px solid #FFFFFF; border-left: 2px solid #FFFFFF; border-right: 2px solid #8995FA; border-bottom: 2px solid #8995FA; } table.rate {font-size:16pt;background-color:#F3F3F3;width:80% } table.maincontent {font-size:11pt;background-color:#F3F3F3;width:80%;border-collapse:collapse;  } table.maincontent td{border:solid #000 1px;} table.content {font-size:10pt;background-color:#F3F3F3;width:80% }
 table.contentG {font-size:11pt;background-color:#FFCCFF;width:80% } table.contentN {font-size:10pt;background-color:#CCCCCC;width:80% } table.footer {font-size:10pt; background-color:#669900;width:80%} P {text-align:left;color:red} .A {width:100%;height:10;text-align:center} .B {text-align:center } .C {color:#AC0000} .D{color:#339933} .E {width:150} .F {font-weight:bold,color:#FF0000} .I {font-weight:bold,color:#FF0000} a:visited{color:blue;text-decoration:none} a:link{text-decoration:none} .H {font-size:11pt;background-color:#669900;}</style>
<html><body><table class=header align='center'><tr><td class=A><a class=F>和黄商贸报障中心 - 通知</a></td></tr></table>
<table class=content align='center'>
<tr><td COLSPAN=2 class=I>
Dear <strong>$customer</strong>,<br><br>
您的报障[$ref->{'subject'}]有更新。<br>
<br>
如果您需要补充意见，可以直接回复此邮件通知支持人员，可以
<a href="mailto:it\@hwccl.com?subject=$urlReply">点击这里回复，请移除签名</a> <br>
<br>	

更新内容/Quote:
<xmp class=D>
$subcontents
</xmp>
</td></tr>
<tr><td class=B>$ref->{'name'}</td><td class=B>$ref->{'total'}</td></tr>
</table>
___CONTENTS___

	#$receipt='marcoy@hwccl.com,suanh@hwccl.com,meansonw@hwccl.com';
	#$receipt='meansonw@hwccl.com';
	sendMail("$receipt","$subject","$contents");
	#print $receipt;
    }
    


	

exit 0;